package com.shop.jpa.support;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * 拼接原生 SQL 参数类
 * 
 * @author Jerry  2018-8-8 12:09:44
 */
@Slf4j
public class QuerySQL {

	public static final int DEFAULT_PAGE_NO = 1;
	public static final int DEFAULT_PAGE_SIZE = 10;
	public static final int MAX_PAGE_SIZE = 65535;

	/** 当前页数 */
	private Integer pageNo;

	/** 每页条数 */
	private Integer pageSize;

	/** 查询SQL */
	private String querySql;

	/** 分页SQL */
	private String pageSql;

	/** 计数SQL */
	private String countSql;

	/** 查询字段SQL块 */
	private String field;

	/** 查询表SQL块 */
	private String from;

	/** 拼接查询条件SQL块 */
	private StringBuffer whereBuffer;

	/** 查询条件SQL块 */
	private String where;

	/** 查询排序SQL块 */
	private String order;

	/** 查询分组SQL块 */
	private String group;

	/** 查询条件参数 */
	private List<Object> paramList;

	@SuppressWarnings("unused")
	private QuerySQL() {}

	/**
	 * 初始化
	 * @param pageNo	当前页数
	 * @param pageSize	每页条数
	 * @param field		查询字段SQL块
	 * @param from		查询对象SQL块
	 * @param order		查询排序SQL块
	 * @param where		查询条件SQL块
	 * @param group		查询分组SQL块
	 * @param paramList	参数列表
	 */
	private void init(Integer pageNo, Integer pageSize, String field, String from,
					  String order, String where, String group, List<Object> paramList) {
		this.pageNo = pageNo;
		if(null == this.pageNo || this.pageNo <= 0) {
			this.pageNo = DEFAULT_PAGE_NO;
		}
		this.pageNo--;

		this.pageSize = pageSize;
		if(null == this.pageSize || this.pageSize <= 0) {
			this.pageSize = DEFAULT_PAGE_SIZE;
		}
		if(this.pageSize > MAX_PAGE_SIZE) {
			this.pageSize = MAX_PAGE_SIZE;
		}

		this.field = field;
		this.from = from;
		this.order = order;
		this.group = group;

		if(StringUtils.isNotBlank(where)) {
			this.whereBuffer = new StringBuffer(" where 1 = 1 ").append(where);
		} else {
			this.whereBuffer = new StringBuffer(" where 1 = 1 ");
		}

		if(null != paramList && paramList.size() > 0) {
			this.paramList =  paramList;
		} else {
			this.paramList =  new ArrayList<>();
		}
	}

	public QuerySQL(String field, String from, String order) {
		this.init(null, null, field, from, order, null, null, null);
	}

	public QuerySQL(String field, String from, String where, String order, List<Object> paramList) {
		this.init(null, null, field, from, order, where, null, paramList);
	}

	public QuerySQL(String field, String from, String order, String group) {
		this.init(null, null, field, from, order, null, group, null);
	}

	public QuerySQL(String field, String from, String where, String order, String group, List<Object> paramList) {
		this.init(null, null, field, from, order, where, group, paramList);
	}

	public QuerySQL(Integer pageNo, Integer pageSize, String field, String from, String order) {
		this.init(pageNo, pageSize, field, from, order, null, null, null);
	}

	public QuerySQL(Integer pageNo, Integer pageSize, String field, String from, String where, String order, List<Object> paramList) {
		this.init(pageNo, pageSize, field, from, order, where, null, paramList);
	}

	public QuerySQL(Integer pageNo, Integer pageSize, String field, String from, String order, String group) {
		this.init(pageNo, pageSize, field, from, order, null, group, null);
	}

	public QuerySQL(Integer pageNo, Integer pageSize, String field, String from, String where, String order, String group, List<Object> paramList) {
		this.init(pageNo, pageSize, field, from, order, where, group, paramList);
	}

	/**
	 * 添加参数值
	 * @param param
	 */
	public void addParam(Object param){
		this.paramList.add(param);
	}

	/**
	 * 添加参数值
	 * @param params
	 */
	public void addParams(List<Object> params){
		this.paramList.addAll(params);
	}

	/**
	 * 添加没有参数的查询条件
	 * @param str
	 */
	public void appendSQL(String str){
		this.whereBuffer.append(" " + str + " ");
	}

	/**
	 * 添加没有参数的查询条件
	 * @param str
	 */
	public void addWhereWithoutParams(String str){
		this.whereBuffer.append(" and " + str + " ");
	}

	/**
	 * 添加可变参数的查询条件
	 * @param whereSQL
	 * @param params
	 */
	public void addWhereParams(String whereSQL, Object... params){
		if(null == params || params.length == 0) {
			this.whereBuffer.append(" and " + whereSQL + " ");
		} else {
			this.whereBuffer.append(" and ( " + whereSQL + " ) ");
			for (Object param : params) {
				this.paramList.add(param);
			}
		}
	}

	/**
	 * 添加可变参数的查询条件
	 * @param whereSQL
	 * @param params
	 */
	public void addWhereLikeParams(String whereSQL, String... params){
		if(null == params || params.length == 0) {
			this.whereBuffer.append(" and " + whereSQL + " ");
		} else {
			this.whereBuffer.append(" and ( " + whereSQL + " ) ");
			for (String param : params) {
				this.paramList.add("%" + param + "%");
			}
		}
	}

	/**
	 * 添加可变参数的查询条件
	 * @param whereSQL
	 * @param params
	 */
	public void addWhereLikeParamsAllNotBlank(String whereSQL, String... params){
		if(null == params || params.length == 0) {
			this.whereBuffer.append(" and " + whereSQL + " ");
		} else {
			boolean isAllBlank = true;
			List<Object> list = new ArrayList<>();
			for (String param : params) {
				if(StringUtils.isNotBlank(param)) {
					isAllBlank = false;
				}
				list.add("%" + param + "%");
			}

			if(isAllBlank) {
				return;
			}

			this.whereBuffer.append(" and ( " + whereSQL + " ) ");
			this.paramList.addAll(list);
		}
	}

	/**
	 * 添加等于的查询条件，如 id = 1
	 * @param key
	 * @param value
	 */
	public void addWhereEq(String key, Object value) {
		this.whereBuffer.append(" and " + key + " = ?");
		this.paramList.add(value);
	}

	/**
	 * 当 value 不为空时，添加等于的查询条件，如 id = 1
	 * @see QuerySQL#addWhereEq(String, Object)
	 * @param key
	 * @param value
	 */
	public void addWhereEqNotEmpty(String key, Object value) {
		if (null != value) {
			addWhereEq(key, value);
		}
	}

	/**
	 * 当 value 不为空字符串时，添加等于的查询条件，如 id = 1
	 * @see QuerySQL#addWhereEq(String, Object)
	 * @param key
	 * @param value
	 */
	public void addWhereEqNotBlank(String key, String value) {
		if (StringUtils.isNotBlank(value)) {
			addWhereEq(key, value);
		}
	}

	/**
	 * 添加不等于的查询条件，如 id <> 1
	 * @param key
	 * @param value
	 */
	public void addWhereNotEq(String key, Object value) {
		this.whereBuffer.append(" and " + key + " <> ?");
		this.paramList.add(value);
	}

	/**
	 * 当 value 不为空时，添加不等于的查询条件，如 id <> 1
	 * @see QuerySQL#addWhereNotEq(String, Object)
	 * @param key
	 * @param value
	 */
	public void addWhereNotEqNotEmpty(String key, Object value) {
		if (null != value) {
			addWhereNotEq(key, value);
		}
	}

	/**
	 * 当 value 不为空字符串时，添加不等于的查询条件，如 id <> 1
	 * @see QuerySQL#addWhereNotEq(String, Object)
	 * @param key
	 * @param value
	 */
	public void addWhereNotEqNotBlank(String key, String value) {
		if (StringUtils.isNotBlank(value)) {
			addWhereNotEq(key, value);
		}
	}

	/**
	 * 添加全模糊的查询条件，如 name like '%xx%'
	 * @param key
	 * @param value
	 */
	public void addWhereLike(String key, String value) {
		this.whereBuffer.append(" and " + key + " like ? ");
		this.paramList.add("%" + value + "%");
	}

	/**
	 * 当 value 不为空字符串时，添加全模糊的查询条件，如 name like '%xx%'
	 * @see QuerySQL#addWhereLike(String, String)
	 * @param key
	 * @param value
	 */
	public void addWhereLikeNotBlank(String key, String value) {
		if (StringUtils.isNotBlank(value)) {
			addWhereLike(key, value);
		}
	}

	/**
	 * 添加左边模糊的查询条件，如 name like '%xx'
	 * @param key
	 * @param value
	 */
	public void addWhereLikeLeft(String key, String value) {
		this.whereBuffer.append(" and " + key + " like ? ");
		this.paramList.add("%" + value);
	}

	/**
	 * 当 value 不为空字符串时，添加左边模糊的查询条件，如 name like '%xx'
	 * @see QuerySQL#addWhereLikeLeft(String, String)
	 * @param key
	 * @param value
	 */
	public void addWhereLikeLeftNotBlank(String key, String value) {
		if (StringUtils.isNotBlank(value)) {
			addWhereLikeLeft(key, value);
		}
	}

	/**
	 * 添加右边模糊的查询条件，如 name like 'xx%'
	 * @param key
	 * @param value
	 */
	public void addWhereLikeRight(String key, String value) {
		this.whereBuffer.append(" and " + key + " like ? ");
		this.paramList.add(value + "%");
	}

	/**
	 * 当 value 不为空字符串时，添加右边模糊的查询条件，如 name like 'xx%'
	 * @see QuerySQL#addWhereLikeRight(String, String)
	 * @param key
	 * @param value
	 */
	public void addWhereLikeRightNotBlank(String key, String value) {
		if (StringUtils.isNotBlank(value)) {
			addWhereLikeRight(key, value);
		}
	}

	/**
	 * 添加小于的查询条件，如 time <= yyyyMMdd 23:59:59
	 * @param key
	 * @param value
	 */
	public void addWhereDateLt(String key, String value) {
		this.whereBuffer.append(" and " + key + " <= STR_TO_DATE(concat(?, ' 23:59:59'), '%Y-%m-%d %H:%i:%s') ");
		this.paramList.add(value);
	}

	/**
	 * 当 value 不为空字符串时，添加小于的查询条件，如 time <= yyyyMMdd 23:59:59
	 * @see QuerySQL#addWhereDateLt(String, String)
	 * @param key
	 * @param value
	 */
	public void addWhereDateLtNotBlank(String key, String value) {
		if (StringUtils.isNotBlank(value)) {
			addWhereDateLt(key, value);
		}
	}

	/**
	 * 添加大于的查询条件，如 time >= yyyyMMdd 00:00:00
	 * @param key
	 * @param value
	 */
	public void addWhereDateGt(String key, String value) {
		this.whereBuffer.append(" and " + key + " >= STR_TO_DATE(concat(?, ' 00:00:00'), '%Y-%m-%d %H:%i:%s') ");
		this.paramList.add(value);
	}

	/**
	 * 当 value 不为空字符串时，添加大于的查询条件，如 time >= yyyyMMdd 00:00:00
	 * @see QuerySQL#addWhereDateGt(String, String)
	 * @param key
	 * @param value
	 */
	public void addWhereDateGtNotBlank(String key, String value) {
		if (StringUtils.isNotBlank(value)) {
			addWhereDateGt(key, value);
		}
	}

	/**
	 * 添加小于的查询条件，如 time <= yyyyMMdd HH:mm:ss
	 * @param key
	 * @param value
	 */
	public void addWhereDateTimeLt(String key, String value) {
		this.whereBuffer.append(" and " + key + " <= STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s') ");
		this.paramList.add(value);
	}

	/**
	 * 当 value 不为空字符串时，添加小于的查询条件，如 time <= yyyyMMdd HH:mm:ss
	 * @see QuerySQL#addWhereDateTimeLt(String, String)
	 * @param key
	 * @param value
	 */
	public void addWhereDateTimeLtNotBlank(String key, String value) {
		if (StringUtils.isNotBlank(value)) {
			addWhereDateTimeLt(key, value);
		}
	}

	/**
	 * 添加大于的查询条件，如 time >= yyyyMMdd HH:mm:ss
	 * @param key
	 * @param value
	 */
	public void addWhereDateTimeGt(String key, String value) {
		this.whereBuffer.append(" and " + key + " >= STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s') ");
		this.paramList.add(value);
	}

	/**
	 * 当 value 不为空字符串时，添加大于的查询条件，如 time >= yyyyMMdd HH:mm:ss
	 * @see QuerySQL#addWhereDateTimeGt(String, String)
	 * @param key
	 * @param value
	 */
	public void addWhereDateTimeGtNotBlank(String key, String value) {
		if (StringUtils.isNotBlank(value)) {
			addWhereDateTimeGt(key, value);
		}
	}

	/**
	 * 添加小于的查询条件，如 age < 1
	 * @param key
	 * @param value
	 */
	public void addWhereLt(String key, Number value) {
		this.whereBuffer.append(" and " + key + " <= ? ");
		this.paramList.add(value);
	}

	/**
	 * 添加小于的查询条件，如 age < 1
	 * @param key
	 * @param value
	 */
	public void addWhereLtNotBlank(String key, String value) {
		if (StringUtils.isNotBlank(value)) {
			this.whereBuffer.append(" and " + key + " <= ? ");
			this.paramList.add(value);
		}
	}

	/**
	 * 添加大于的查询条件，如 age > 1
	 * @param key
	 * @param value
	 */
	public void addWhereGt(String key, Number value) {
		this.whereBuffer.append(" and " + key + " >= ? ");
		this.paramList.add(value);
	}

	/**
	 * 添加大于的查询条件，如 age > 1
	 * @param key
	 * @param value
	 */
	public void addWhereGtNotBlank(String key, String value) {
		if (StringUtils.isNotBlank(value)) {
			this.whereBuffer.append(" and " + key + " >= ? ");
			this.paramList.add(value);
		}
	}

	/**
	 * 当前页数
	 * @return
	 */
	public Integer getPageNo() {
		return this.pageNo;
	}

	/**
	 * 每页条数
	 * @return
	 */
	public Integer getPageSize() {
		return this.pageSize;
	}

	/**
	 * 获取查询字段SQL块
	 * @return
	 */
	public String getField() {
		return this.field;
	}

	/**
	 * 获取查询表SQL块
	 * @return
	 */
	public String getFrom() {
		return this.from;
	}

	/**
	 * 获取查询条件SQL块
	 * @return
	 */
	public String getWhere() {
		if(StringUtils.isBlank(this.where)) {
			this.where = this.whereBuffer.toString();
		}
		return this.where;
	}

	/**
	 * 获取集合形式的查询参数
	 * @return
	 */
	public List<Object> getParamList() {
		return this.paramList;
	}

	/**
	 * 获取数组形式的查询参数
	 * @return
	 */
	public Object[] getParams() {
		if(null == this.paramList) return null;

		return this.paramList.toArray();
	}

	private void appendOrder(StringBuffer sqlBuffer) {
		if(StringUtils.isNotBlank(this.order)) {
			sqlBuffer.append(" order by ").append(this.order);
		}
	}

	private void appendGroup(StringBuffer sqlBuffer) {
		if(StringUtils.isNotBlank(this.group)) {
			sqlBuffer.append(" group by ").append(this.group);
		}
	}

	private void appendPageParam(StringBuffer sqlBuffer) {
		sqlBuffer.append(" limit ").append(this.pageNo * this.pageSize).append(", ").append(pageSize);
	}

	/**
	 * 获取查询SQL
	 * @return
	 */
	public String getQuerySql() {
		if(StringUtils.isBlank(this.querySql)) {
			StringBuffer sqlBuffer = new StringBuffer("select ").append(this.field).append(" from ")
					.append(this.from).append(this.whereBuffer);
			appendGroup(sqlBuffer);
			appendOrder(sqlBuffer);
			this.querySql = sqlBuffer.toString();
		}
		log.debug(this.querySql);
		return this.querySql;
	}

	/**
	 * 获取查询SQL
	 * @return
	 */
	public String getCountSql() {
		if(StringUtils.isBlank(this.countSql)) {
			StringBuffer sqlBuffer = new StringBuffer("select ");
			if(StringUtils.isBlank(this.group)) {
				sqlBuffer = sqlBuffer.append(" count(*) ").append(" from ")
						.append(this.from)
						.append(this.whereBuffer);
			} else {
				sqlBuffer = sqlBuffer.append(" count(*) ").append(" from ")
						.append(" ( select 1 from ")
						.append(this.from)
						.append(this.whereBuffer);
				appendGroup(sqlBuffer);
				sqlBuffer.append(") t_c_");
			}
			this.countSql = sqlBuffer.toString();
		}
		log.debug(this.countSql);
		return this.countSql;
	}

	/**
	 * 获取分页SQL
	 *
	 * @since		此处暂时使用MySQL语法，待后期需要其他数据库支持时再进行扩展
	 * @return
	 */
	public String getPageSql() {
		if(StringUtils.isBlank(this.pageSql)) {
			StringBuffer sqlBuffer = new StringBuffer("select ").append(this.field).append(" from ")
					.append(this.from).append(this.whereBuffer);
			appendGroup(sqlBuffer);
			appendOrder(sqlBuffer);
			appendPageParam(sqlBuffer);
			this.pageSql = sqlBuffer.toString();
		}
		log.debug(this.pageSql);
		return this.pageSql;
	}

}
